-- DROP FUNCTION cross_tab(text, text);

CREATE OR REPLACE FUNCTION cross_tab(sql_query text, dest_table text)
  RETURNS text AS
$BODY$
""" -------------------------[ cross_tab-3.1 ]------------------------------- 

DESCRIPTION
Returns a crosstab table from a normalized table (similar to MS Access's 
pivot table). 
Requirements: An SQL returning a normalized table with the three 
columns corresponding to row header, column header, and value. Additonal 
columns are ignored.
Each combination of row header and column header should be unique.

ARGUMENTS
sql_query:  A string containing an SQL statement returning the normalized 
            table to transform.
            The first three columns should correspond to the row header,
            column header, value.
            Further columns returned by sql_query are ignored.

dest_table: Name of the output table that will store the cross-tab query.

"""

" -----------------------[ Parameters to connect to database ]--------------- "
import psycopg2
import sys
import datetime

database_name= 'CAGE'
user_name= 'dberaldi'
host_name= 'localhost'
password_db= 'mypassword'

# Connection to database
connection_parameters= 'dbname=' + database_name + ' user=' + user_name + ' host=' + host_name + ' password=' + password_db
try:
    conn = psycopg2.connect(connection_parameters);
except:
    return("I am unable to connect to the database with: " + connection_parameters)

cur = conn.cursor()

" -------------------[ Get input table column names and datatypes ]--------- "

qry_input_table= 'CREATE TEMP TABLE tmp_cross_tab_input AS (SELECT * FROM (' + sql_query + ') AS t1 LIMIT 1);'
cur.execute(qry_input_table)

## w/o distinct it returns each name twice (!?)
qry_names_ord= """SELECT DISTINCT column_name, ordinal_position, data_type
  FROM information_schema.columns 
  WHERE table_name like 'tmp_cross_tab_input' 
  ORDER BY ordinal_position;"""
cur.execute(qry_names_ord)
col_info= cur.fetchall()
col_names= [x[0] for x in col_info]
col_types= [x[2] for x in col_info]

## Indexes of headers:
ri_s= 0 ## Row header starts
ri_e= len(col_names)-2 ## Row header finishes
ri= range(ri_s, ri_e)
ci= -2
vi= -1

## Default header names:
def quoter(x):
    " For each colname in row header convert to str and embrace in quotes"
    return('"' + str(x) + '"')
row_header= ', '.join([quoter(x) for x in col_names[ri_s:ri_e]]) ## All columns but last two
column_header= '"' + str(col_names[ci]) + '"' ## Second last columns  in input query
value= '"' + str(col_names[vi]) + '"' ## Last columns

## String to plug in SELECT statement
row_header__col_header__value= ', '.join([row_header, column_header + '::text', value])

## Collect column header names
qry_column_header= 'SELECT DISTINCT ' + column_header + '::text FROM (' + sql_query + ') AS t1;'
cur.execute(qry_column_header)
headers= cur.fetchall()
headers.sort()
headers= [x[0] for x in headers]

## Dictionary with position of each column header
column_dict={}
i= len(ri) ## Start from after the row_headings
for key in headers:
    column_dict[key] = i
    i += 1

## Prepare CREATE TABLE crosstab table

qry_create_crosstab= 'CREATE TABLE "' + dest_table + '"(\n  ' ## + row_header + ' ' + col_types[ri_s:ri_e]
for i in ri:
    col= '"' + col_names[i] + '"'
    var= col_types[i]
    qry_create_crosstab= qry_create_crosstab + col + ' ' + var + ',\n '
qry_create_crosstab= qry_create_crosstab.rstrip(',\n ')

for col in headers:
    col_def=  ',\n  '+ '"' + col + '" ' + col_types[vi]
    qry_create_crosstab= qry_create_crosstab + col_def
qry_create_crosstab= qry_create_crosstab + ');'
cur.execute(qry_create_crosstab)

## INSERT statement
qry_insert= 'INSERT INTO "' + dest_table + '" VALUES ('
cur2 = conn.cursor()

## Some functions for crosstabing
def reformat(x):
    " Substitute python's None with empty string or False if datatype is boolean "
    " Substitute date-times to isoformat "
    if x is None:
        if col_types[vi] == 'boolean':
            return(False)
        else:
            return('')
    elif type(x) == type(datetime.datetime.now()):
        return(datetime.date.isoformat(x))
    else:
        return(x)

def insert():
    " Reformat each block of input lines to a single line to be sent to INSERT "
    outlinex= [reformat(x) for x in outline]
    insert_line= (str(outlinex)).strip('[]')
    cur2.execute(qry_insert + insert_line + ');')
## Uncomment this if you want to send the output table to file
##    outfile.write('\t'.join([str(x) for x in outline]) + '\n')

## Stop execution if two rows have the same Row and column header (i.e. there are multiple values/row)
def check_duplicate():
    if (line is not None) and (current_row_heading == [line[i] for i in ri]) and (current_col_heading == line[ci]):
        sys.exit('Duplicate found for line\n' + str(line))

## Order input query
qry_input_ordered= 'SELECT ' + row_header__col_header__value + ' FROM (' + sql_query + ') AS t1 ORDER BY ' + row_header + ', '+ column_header +';'
cur.execute(qry_input_ordered)

" --------------------------[ Start cross-tabulation ]----------------------- "
# Uncomment to send output to file
# outfile= open('/tmp/cross_tab_out.txt', 'w')

line= cur.fetchone()
while line is not None:
    current_row_heading= [line[i] for i in ri]
    current_col_heading= line[ci]
    current_value= line[vi]
    outline= [None] * (len(headers)+ri_e)  ## List to hold row heading and crossed values
    for i in ri:
        " Replace in empty outline the row headings "
        outline[i]=current_row_heading[i]
    value_pos= column_dict[current_col_heading] ## Insert the current value at this position in the list to match the corresponding column in norm table
    outline[value_pos]= current_value
    line= cur.fetchone()
    check_duplicate()
    if line is None:
        insert()
        break
    while [line[i] for i in ri] == current_row_heading:
        current_col_heading= line[ci]
        current_value= line[vi]
        value_pos= column_dict[current_col_heading] ## Insert the current value at this position in the list to match the corresponding column in norm table
        outline[value_pos]= current_value
        line= cur.fetchone()
        check_duplicate()
        if line is None:
            insert()
            break
    if line is None:
        break
    insert()
#    outlinex= [reformat(x) for x in outline]
#    insert_line= (str(outlinex)).strip('[]')
#    return(qry_insert + insert_line + ');')
# outfile.close()

qry_comment= 'COMMENT ON TABLE "' + dest_table + '" IS ' + "$comment$ Crosstab table generated by cross_tab() from input query: " + sql_query + " $comment$;"
cur.execute(qry_comment)
conn.commit()

return('Cross-tab table written to "' + dest_table + 
       '".\nThe following SQL commands have been executed:\n\n' +
       qry_input_table + '\n\n' + qry_names_ord + '\n\n' + 
       qry_column_header + '\n\n' + qry_input_ordered +'\n\n' + 
       qry_insert + "'line from input');" + '\n\n' + qry_comment)

$BODY$
  LANGUAGE 'plpythonu';

